<html>
 <head>
  <meta charset="UTF-8">
 </head>
 <body>
  <h3 data-lake-id="AAa2k" id="AAa2k"><span data-lake-id="ua46f6904" id="ua46f6904">背景</span></h3>
  <p data-lake-id="u27356deb" id="u27356deb"><br></p>
  <p data-lake-id="ud2ea20d3" id="ud2ea20d3"><span data-lake-id="u8966894e" id="u8966894e">在一个后台管理功能中，需要导出Excel，但是当处理大数据量的Excel文件导出时，常用的Apache POI库可能因其内存占用较高而导致内存溢出问题。同时，数据处理过程可能非常耗时，导致用户等待时间过长或请求超时。为解决这些问题，采用了基于 EasyExcel 和线程池的解决方案。</span></p>
  <p data-lake-id="u315d812f" id="u315d812f"><span data-lake-id="uda93b6a3" id="uda93b6a3">​</span><br></p>
  <p data-lake-id="u784b3921" id="u784b3921"><br></p>
  <h3 data-lake-id="oiiTY" id="oiiTY"><span data-lake-id="ud48627d8" id="ud48627d8">技术选型</span></h3>
  <p data-lake-id="u8ba8296b" id="u8ba8296b"><br></p>
  <p data-lake-id="u4909f299" id="u4909f299"><span data-lake-id="u309edcb1" id="u309edcb1">Excel的导出很多种方案，包括了POI、EasyExcel还有Hutool中也有类似的功能。在市面上，用的最多的还是POI和EasyExcel，而在处理大文件这方面，EasyExcel更加适合一些。</span></p>
  <p data-lake-id="u6ac8adce" id="u6ac8adce"><span data-lake-id="ucef12290" id="ucef12290">​</span><br></p>
  <p data-lake-id="u41355bf4" id="u41355bf4"><span data-lake-id="ub3061935" id="ub3061935">在文件导出过程中，用异步的方式进行，用户不需要在页面一直等待。异步文件生成之后，把文件上传到云存储中，再通知用户去下载即可。</span></p>
  <p data-lake-id="u99d22384" id="u99d22384"><span data-lake-id="u4f35eedc" id="u4f35eedc">​</span><br></p>
  <p data-lake-id="u140013d4" id="u140013d4"><span data-lake-id="u4c817870" id="u4c817870">这里云存储选择阿里云的OSS，线程池异步处理采用@Async</span></p>
  <p data-lake-id="u9bede299" id="u9bede299"><span data-lake-id="ub9187ab4" id="ub9187ab4">​</span><br></p>
  <p data-lake-id="ucdd113dc" id="ucdd113dc"><span data-lake-id="u29af1393" id="u29af1393">用户通知这里就是用Spring Mail进行邮件发送即可。</span></p>
  <p data-lake-id="uf6564eda" id="uf6564eda"><br></p>
  <h3 data-lake-id="m8zeF" id="m8zeF"><span data-lake-id="ud4448e49" id="ud4448e49">具体实现</span></h3>
  <p data-lake-id="u4591f376" id="u4591f376"><br></p>
  <p data-lake-id="uf376a112" id="uf376a112"><span data-lake-id="udd6e9215" id="udd6e9215">入口是一个Controller，主要接收用户的文件导出请求。</span></p>
  <p data-lake-id="u2f73783a" id="u2f73783a"><span data-lake-id="u1caf59e3" id="u1caf59e3">​</span><br></p>
  <pre lang="java"><code>
@RestController
@RequestMapping("/export")
public class DataExportController {

    @Autowired
    private ExcelExportService exportService;

    @GetMapping("/data")
    public ResponseEntity&lt;String&gt; exportData() {
        List&lt;DataModel&gt; data = fetchData();
        String fileUrl = exportService.exportDataAsync(data);

        return ResponseEntity.ok("导出任务开始，文件生成后会通知您下载链接");
    }

    private List&lt;DataModel&gt; fetchData() {
        // 获取需要导出的数据
    }
}

</code></pre>
  <p data-lake-id="u77dcf4ec" id="u77dcf4ec"><br></p>
  <p data-lake-id="ub076af0e" id="ub076af0e"><span data-lake-id="u0267a128" id="u0267a128">这里做了一些简化，比如筛选条件、以及具体的获取数据部分我都省略了，大家可以根据自己的业务情况来实现。</span></p>
  <p data-lake-id="u4e505d15" id="u4e505d15"><br></p>
  <p data-lake-id="u46cbb80c" id="u46cbb80c"><span data-lake-id="ue2613a8d" id="ue2613a8d">下面是导出服务的具体实现:</span></p>
  <p data-lake-id="u40e0a7f6" id="u40e0a7f6"><span data-lake-id="ue67b3efb" id="ue67b3efb">​</span><br></p>
  <pre lang="java"><code>
@Service
public class ExcelExportService {

    @Async("exportExecutor")
    public String exportDataAsync(List&lt;DataModel&gt; data) {
        // 生成 Excel 文件并获取 InputStream
        InputStream fileContent = generateExcelFile(data);
        String fileName = "data_" + System.currentTimeMillis() + ".xlsx";
        
        // 上传到 OSS
        String fileUrl = ossService.uploadFile(fileName, fileContent);
        // 发送邮件
        emailService.sendEmail(data.getUserEmail(), "文件导出通知", "您的文件已导出，下载链接: " + fileUrl);
        return fileUrl;
    }

   private InputStream generateExcelFile(List&lt;DataModel&gt; data) {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            ExcelWriterBuilder writerBuilder = EasyExcel.write(outputStream, DataModel.class);
            writerBuilder.sheet("Data").doWrite(data);
        } catch (Exception e) {
            // 处理异常
        }
        return new ByteArrayInputStream(outputStream.toByteArray());
    }

    // DataModel 类定义
    public static class DataModel {
        //省略参数及setter/getter
    }
}

</code></pre>
  <p data-lake-id="ue921109e" id="ue921109e"><br></p>
  <p data-lake-id="ua7702338" id="ua7702338"><span data-lake-id="u09e715aa" id="u09e715aa">这里面用到了@Async来实现一个异步处理，这里主要干了三件事：</span></p>
  <ul list="u1c0d2265">
   <li fid="u5896afe7" data-lake-id="u2bc89204" id="u2bc89204"><span data-lake-id="u4df5f626" id="u4df5f626">使用EasyExcel生成文件</span></li>
   <li fid="u5896afe7" data-lake-id="uc6b12264" id="uc6b12264"><span data-lake-id="u7641260f" id="u7641260f">OSS上传生成后的文件</span></li>
   <li fid="u5896afe7" data-lake-id="u9fa99878" id="u9fa99878"><span data-lake-id="u6dfd4b47" id="u6dfd4b47">给用户发邮件通知下载地址</span></li>
  </ul>
  <p data-lake-id="ucb533f13" id="ucb533f13"><span data-lake-id="ub89163a8" id="ub89163a8">​</span><br></p>
  <p data-lake-id="u8fd069c7" id="u8fd069c7"><span data-lake-id="ua5301443" id="ua5301443">这里为了用到真正的线程池，制定了一个自定义的exportExecutor，实现如下:</span></p>
  <p data-lake-id="u478017b0" id="u478017b0"><br></p>
  <pre lang="java"><code>
@Configuration
@EnableAsync
public class AsyncExecutorConfig {
    @Bean("exportExecutor")
    public Executor exportExecutor() {

        ThreadFactory namedThreadFactory = new ThreadFactoryBuilder()
                .setNameFormat("registerSuccessExecutor-%d").build();

        ExecutorService executorService = new ThreadPoolExecutor(10, 20,
                0L, TimeUnit.MILLISECONDS,
                new LinkedBlockingQueue&lt;Runnable&gt;(1024), namedThreadFactory, new ThreadPoolExecutor.AbortPolicy());

        return executorService;
    }

}

</code></pre>
  <p data-lake-id="ud7f7e904" id="ud7f7e904"><br></p>
  <p data-lake-id="ufa4a3ea3" id="ufa4a3ea3"><span data-lake-id="u782445d1" id="u782445d1">OSS上传服务部分代码实现如下，依赖阿里云OSS的API进行文件上传：</span></p>
  <p data-lake-id="u3c0aac99" id="u3c0aac99"><span data-lake-id="u970bc2fe" id="u970bc2fe">​</span><br></p>
  <pre lang="java"><code>
import com.aliyun.oss.OSS;
import com.aliyun.oss.OSSClientBuilder;
import com.aliyun.oss.model.PutObjectRequest;

import java.io.InputStream;
import java.net.URL;
import java.util.Date;

public class OssService {

    private String endpoint = "&lt;OSS_ENDPOINT&gt;";
    private String accessKeyId = "&lt;ACCESS_KEY_ID&gt;";
    private String accessKeySecret = "&lt;ACCESS_KEY_SECRET&gt;";
    private String bucketName = "&lt;BUCKET_NAME&gt;";

    public String uploadFile(String fileName, InputStream fileContent) {
        OSS ossClient = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
        try {
            ossClient.putObject(new PutObjectRequest(bucketName, fileName, fileContent));
            // 设置URL过期时间为1小时
            Date expiration = new Date(System.currentTimeMillis() + 3600 * 1000);
            URL url = ossClient.generatePresignedUrl(bucketName, fileName, expiration);
            return url.toString();
        } finally {
            if (ossClient != null) {
                ossClient.shutdown();
            }
        }
    }
}

</code></pre>
  <p data-lake-id="uc644cbcd" id="uc644cbcd"><br></p>
  <p data-lake-id="u774b436c" id="u774b436c"><span data-lake-id="u3e588afa" id="u3e588afa">邮件发送部分实现：</span></p>
  <p data-lake-id="u234f426a" id="u234f426a"><span data-lake-id="u24c8e864" id="u24c8e864">​</span><br></p>
  <pre lang="java"><code>
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.SimpleMailMessage;
import org.springframework.stereotype.Service;

@Service
public class EmailNotificationService {

    @Autowired
    private JavaMailSender mailSender;

    public void sendEmail(String toAddress, String subject, String body) {
        SimpleMailMessage message = new SimpleMailMessage();
        message.setFrom("noreply@example.com");
        message.setTo(toAddress);
        message.setSubject(subject);
        message.setText(body);

        mailSender.send(message);
    }
}

</code></pre>
  <p data-lake-id="u139affbd" id="u139affbd"><br></p>
  <p data-lake-id="u68b0e498" id="u68b0e498"><span data-lake-id="u7e36e458" id="u7e36e458">还需要一些额外的Spring Mail的配置，配置到</span><strong><span data-lake-id="udee3bbb3" id="udee3bbb3" class="lake-fontsize-11" style="color: rgb(15, 15, 15)">application.properties</span></strong><span data-lake-id="uf7e09896" id="uf7e09896">：</span></p>
  <p data-lake-id="u27674200" id="u27674200"><span data-lake-id="u0d105beb" id="u0d105beb">​</span><br></p>
  <pre lang="java"><code>
spring.mail.host=smtp.example.com
spring.mail.port=587
spring.mail.username=user@example.com
spring.mail.password=yourpassword
spring.mail.properties.mail.smtp.auth=true
spring.mail.properties.mail.smtp.starttls.enable=true
</code></pre>
  <p data-lake-id="u23ff61df" id="u23ff61df"><br></p>
  <h3 data-lake-id="fTya9" id="fTya9"><span data-lake-id="u8c285800" id="u8c285800">学习资料</span></h3>
  <p data-lake-id="u9ccd8850" id="u9ccd8850"><br></p>
  <p data-lake-id="udd9547ac" id="udd9547ac"><br></p>
  <p data-lake-id="u9abfd9d9" id="u9abfd9d9"><br></p>
  <p data-lake-id="u71147565" id="u71147565"><br></p>
  <p data-lake-id="u04c3a73b" id="u04c3a73b"><br></p>
 </body>
</html>